package com.sxfq.loansupermarket.backsystem.modular.analysis.controller;

import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Analysis;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.CustomerPlatformVisitInfo;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.modular.analysis.dao.CountDao;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;

/**
 * 数据统计控制器
 *
 * @author fengshuonan
 * @Date 2017-07-19 16:23:35
 */
@Controller
@RequestMapping("/count")
public class CountController extends BaseController {

	private String PREFIX = "/analysis/count/";
	@Resource
	private CountDao countDao;
	@Resource
	SqlMapper sqlMapper;

	/**
	 * 跳转到数据统计首页
	 */
	@RequestMapping("")
	public String index() {
		return PREFIX + "count.html";
	}

	@RequestMapping("platform")
	public String platform() {
		return PREFIX + "count_platform.html";
	}

	@RequestMapping("register")
	public String register() {
		return PREFIX + "count_register.html";
	}

	@RequestMapping("/query/{abbreviation}")
	public String query(@PathVariable String abbreviation, Model model) {
		model.addAttribute("jiancheng", abbreviation);
		return "/platform/loanProduct/loanProduct.html";
	}

	/**
	 * 跳转到添加数据统计
	 */
	@RequestMapping("/count_add")
	public String countAdd() {
		return PREFIX + "count_add.html";
	}

	/**
	 * 跳转到修改数据统计
	 */
	@RequestMapping("/count_update/{countId}")
	public String countUpdate(@PathVariable Integer countId, Model model) {
		return PREFIX + "count_edit.html";
	}

	/**
	 * 获取数据统计列表
	 */
	@RequestMapping(value = "/list")
	@ResponseBody
	public Object list(@RequestParam(required = false) String beginTime, @RequestParam(required = false) String endTime,
			@RequestParam(required = false) String jiancheng) {
		Page<Analysis> page = new PageFactory<Analysis>().defaultPage();
		List<Map<String, Object>> list = null;
		if (jiancheng != null && jiancheng != "") {
			String sql = "select distinct b.id from platform_company_info a inner join platform_product_info b on a.id=b.company_id  where a.abbreviation like '%"
					+ jiancheng + "%'";
			list = sqlMapper.selectList(sql);
		}
		StringBuilder query = new StringBuilder();
		query.append("select count(1) sum,type,platform_id from customer_platform_visit_info a  where 1=1 ");
		if (beginTime != null && endTime != null && endTime != "" && beginTime != "") {
			query.append("and (a.create_time between CONCAT('" + beginTime + ", 00:00:00') and CONCAT('" + endTime
					+ ", 23:59:59')) ");
		}
		if (list != null && list.size() > 0) {
			query.append(" and a.platform_id in(");
			for (int i = 0; i < list.size(); i++) {
				query.append(String.valueOf(list.get(i).get("id")));
				if (i < list.size() - 1) {
					query.append(",");
				}
			}
			query.append(")");
		}
		query.append("group by type,platform_id");
		List<CustomerPlatformVisitInfo> result = sqlMapper.selectList(query.toString(),
				CustomerPlatformVisitInfo.class);
		// List<CustomerPlatformVisitInfo> result = countDao.getCount(beginTime, endTime, query.toString());

		page.setRecords(CountDel(result));
		return super.packForBT(page);
	}

	public List<Analysis> excel(String beginTime, String endTime, String jiancheng) {
		List<Map<String, Object>> list = null;
		if (jiancheng != null && jiancheng != "") {
			String sql = "select distinct b.id from platform_company_info a inner join platform_product_info b on a.id=b.company_id  where a.abbreviation like '%"
					+ jiancheng + "%'";
			list = sqlMapper.selectList(sql);
		}
		StringBuilder query = new StringBuilder();
		query.append(
				"select count(1) sum,t(List<Analysis>) CountDel(result)from customer_platform_visit_info a  where 1=1 ");
		if (beginTime != null && endTime != null && endTime != "" && beginTime != "") {
			query.append("and (a.create_time between CONCAT('" + beginTime + ", 00:00:00') and CONCAT('" + endTime
					+ ", 23:59:59')) ");
		}
		if (list != null && list.size() > 0) {
			query.append(" and a.platform_id in(");
			for (int i = 0; i < list.size(); i++) {
				query.append(String.valueOf(list.get(i).get("id")));
				if (i < list.size() - 1) {
					query.append(",");
				}
			}
			query.append(")");
		}
		query.append("group by type,platform_id");
		List<CustomerPlatformVisitInfo> result = sqlMapper.selectList(query.toString(),
				CustomerPlatformVisitInfo.class);

		// ana.setType4(result.get);

		return CountDel(result);
	}

	public List<Analysis> CountDel(List<CustomerPlatformVisitInfo> list) {
		// Long type1 = list.stream().filter(e -> e.getType() == 1).count();
		// Long type2 = list.stream().filter(e -> e.getType() == 2).count();
		// Long type3 = list.stream().filter(e -> e.getType() == 3).count();
		Map<Object, List<CustomerPlatformVisitInfo>> group = list.stream()
				.filter(e -> e.getType() != 1 && e.getType() != 2 && e.getType() != 3)
				.collect(Collectors.groupingBy(e -> e.getPlatformId()));
		List<Analysis> analysis = new ArrayList<Analysis>();
		String sb;
		String sb1;
		for (Map.Entry<Object, List<CustomerPlatformVisitInfo>> entry : group.entrySet()) {
			list = entry.getValue();
			Long type4 = list.stream().filter(e -> e.getType() == 4).mapToLong(e -> e.getSum()).sum();
			Long type5 = list.stream().filter(e -> e.getType() == 5).mapToLong(e -> e.getSum()).sum();
			Long type6 = list.stream().filter(e -> e.getType() == 6).mapToLong(e -> e.getSum()).sum();
			Long type7 = list.stream().filter(e -> e.getType() == 7).mapToLong(e -> e.getSum()).sum();
			sb1 = "select product_no,company_id from platform_product_info where id= " + String.valueOf(entry.getKey());
			Map<String, Object> ppi = sqlMapper.selectOne(sb1);
			Analysis ana = new Analysis();
			if (!CommUtils.isNull(ppi)) {
				ana.setBianhao(String.valueOf(ppi.get("product_no") == null ? "--" : ppi.get("product_no")));
			} else {
				ana.setBianhao("--");
			}
			sb = "select  abbreviation from platform_company_info where id= " + String.valueOf(ppi.get("company_id"));
			Map<String, Object> pci = sqlMapper.selectOne(sb);
			// ana.setBianhao(String.valueOf(entry.getKey()));
			if (!CommUtils.isNull(pci)) {
				ana.setJiancheng(String.valueOf(pci.get("abbreviation") == null ? "--" : pci.get("abbreviation")));
			} else {
				ana.setJiancheng("--");
			}
			// ana.setType1(type1);
			// ana.setType2(type2);
			// ana.setType3(type3);
			ana.setType4(type4);
			ana.setType5(type5);
			ana.setType6(type6);
			ana.setType7(type7);
			analysis.add(ana);
		}
		return analysis;
	}

	/**
	 * 获取数据统计列表
	 */
	@RequestMapping(value = "/listcount")
	@ResponseBody
	public HashMap<String, Long> listcount(@RequestParam(required = false) String beginTime,
			@RequestParam(required = false) String endTime, @RequestParam(required = false) String jiancheng) {
		List<CustomerPlatformVisitInfo> result = countDao.getCount(beginTime, endTime, jiancheng);
		Long type1 = result.stream().filter(e -> e.getType() == 1).mapToLong(e -> e.getSum()).sum();
		Long type2 = result.stream().filter(e -> e.getType() == 2).mapToLong(e -> e.getSum()).sum();
		Long type3 = result.stream().filter(e -> e.getType() == 3).mapToLong(e -> e.getSum()).sum();
		HashMap<String, Long> a = new HashMap<String, Long>();
		a.put("type1", type1);
		a.put("type2", type2);
		a.put("type3", type3);
		return a;
	}

	/**
	 * 新增数据统计
	 */
	@RequestMapping(value = "/add")
	@ResponseBody
	public Object add() {
		return super.SUCCESS_TIP;
	}

	/**
	 * 删除数据统计
	 */
	@RequestMapping(value = "/delete")
	@ResponseBody
	public Object delete() {
		return SUCCESS_TIP;
	}

	/**
	 * 修改数据统计
	 */
	@RequestMapping(value = "/update")
	@ResponseBody
	public Object update() {
		return super.SUCCESS_TIP;
	}

	/**
	 * 数据统计详情
	 */
	@RequestMapping(value = "/detail")
	@ResponseBody
	public Object detail() {
		return null;
	}

	@RequestMapping(value = "/downloadExcel")
	public void downloadChannelStatisticExcel(@RequestParam(required = false) String beginTime,
			@RequestParam(required = false) String endTime, @RequestParam(required = false) String jiancheng,
			HttpServletResponse response) {

		try {

			List<Analysis> lists = excel(beginTime, endTime, jiancheng);

			// 解析成excel文件
			// 第一步，创建一个webbook，对应一个Excel文件
			HSSFWorkbook wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			HSSFSheet sheet = wb.createSheet("点击量数据统计");

			for (int i = 0; i < 6; i++) {
				sheet.setColumnWidth(i, 24 * 256);
			}
			// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
			HSSFRow row = sheet.createRow(0);
			// 第四步，创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

			HSSFCell cell = row.createCell(0);
			cell.setCellValue("产品编号");
			cell.setCellStyle(style);
			cell = row.createCell(1);
			cell.setCellValue("简称");
			cell.setCellStyle(style);
			cell = row.createCell(2);
			cell.setCellValue("首页点击量");
			cell.setCellStyle(style);
			cell = row.createCell(3);
			cell.setCellValue("推荐产品点击量");
			cell.setCellStyle(style);
			cell = row.createCell(4);
			cell.setCellValue("详情分享点击量");
			cell.setCellStyle(style);
			cell = row.createCell(5);
			cell.setCellValue("申请点击量");
			cell.setCellStyle(style);

			// 填充数据
			for (int i = 0; i < lists.size(); i++) {
				Analysis map = lists.get(i);
				row = sheet.createRow(i + 1);
				// 第四步，创建单元格，并设置值
				row.createCell(0).setCellValue(map.getBianhao());
				row.createCell(1).setCellValue(map.getJiancheng());
				row.createCell(2).setCellValue(map.getType4());
				row.createCell(3).setCellValue(map.getType5());
				row.createCell(4).setCellValue(map.getType6());
				row.createCell(5).setCellValue(map.getType7());
			}
			String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date());
			String filename = "data" + date + ".xls";
			response.setHeader("Content-Disposition",
					"attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
			wb.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
}
